﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Linq.Expressions;
using System.Text;
using System.Threading.Tasks;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
using WinFormAssigment.Entities;

namespace WinFormAssigment.DAO
{
    class DatabaseInteraction
    {
        public static string strConnect = ConfigurationManager.ConnectionStrings["dbConnectionString"].ConnectionString;
        private static SqlConnection connect;
        private static SqlCommand comand;
        private static SqlDataReader reader;
        public static string message;

        public static SqlCommand command
        {
            get { return comand; }
            set { comand = value; }
        }

        public static SqlConnection GetConnectionDB()
        {
            try
            {
                connect = new SqlConnection(strConnect);
            }
            catch (Exception e)
            {
                //message = "Connection strings was wrong !";
                message = e.Message;
                return null;
            }
            return connect;
        }

        //Get one teacher from DB with two input parameters: username and password

        public static Teacher GetTeacherLogin(string user, string pass)
        {
            Teacher teacher;
            try
            {
                using (connect = GetConnectionDB())
                {
                    connect.Open();
                    using (command = new SqlCommand(Query.teacherLogin, connect))
                    {
                        command.CommandType = CommandType.StoredProcedure;
                        command.Parameters.AddWithValue(Query.username, user);
                        command.Parameters.AddWithValue(Query.password, pass);
                        reader = command.ExecuteReader();
                        reader.Read();
                        string teacherID = reader.GetString(0);
                        string password = reader.GetString(1);
                        string name = reader.GetString(2);
                        string gender = reader.GetString(3);
                        DateTime dob = reader.GetDateTime(4);
                        string address = reader.GetString(5);
                        string email = reader.GetString(6);
                        string phone = reader.GetString(7);
                        string description = reader.GetString(8);
                        teacher = new Teacher()
                        {
                            TeacherID = teacherID,
                            Password = password,
                            Name = name,
                            Gender = gender,
                            Dob = dob,
                            Address = address,
                            Email = email,
                            Phone = phone,
                            Description = description
                        };
                        return teacher;
                    }
                }
            }
            catch (SqlException)
            {
                message = "Can not find stored procedure !";
            }

            finally
            {
                if (reader != null)
                {
                    reader.Close();

                }
                if (connect != null)
                {
                    connect.Close();
                }
            }
            return null;

        }

        public static Manager GetManagerLogin(string user, string pwd)
        {
            Manager manager;
            try
            {
                using (connect = GetConnectionDB())
                {
                    connect.Open();
                    using (command = new SqlCommand(Query.managerLogin, connect))
                    {
                        command.CommandType = CommandType.StoredProcedure;
                        command.Parameters.AddWithValue(Query.username, user);
                        command.Parameters.AddWithValue(Query.password, pwd);
                        reader = command.ExecuteReader();
                        reader.Read();

                        string managerID = reader.GetString(0);
                        string password = reader.GetString(1);
                        string name = reader.GetString(2);
                        string gender = reader.GetString(3);
                        DateTime dob = reader.GetDateTime(4);
                        string address = reader.GetString(5);
                        string email = reader.GetString(6);
                        string phone = reader.GetString(7);
                        string description = reader.GetString(8);
                        manager = new Manager()
                        {
                            ManagerID = managerID,
                            Password = password,
                            Name = name,
                            Gender = gender,
                            Dob = dob,
                            Address = address,
                            Email = email,
                            Phone = phone,
                            Description = description
                        };
                        return manager;
                    }
                }
            }
            catch (SqlException)
            {
                message = "Can not find stored procedure !";
            }

            finally
            {
                if (reader != null)
                {
                    reader.Close();

                }
                if (connect != null)
                {
                    connect.Close();
                }
            }
            return null;

        }


        public static List<Teacher> GetAllTeacher()
        {
            List<Teacher> list = new List<Teacher>();
            try
            {
                using (connect = GetConnectionDB())
                {
                    connect.Open();
                    using (command = new SqlCommand("sp_GetAllTeacher", connect))
                    {
                        command.CommandType = CommandType.StoredProcedure;
                        reader = command.ExecuteReader();
                        while (reader.Read())
                        {
                            string teacherID = reader.GetString(0);
                            string password = reader.GetString(1);
                            string name = reader.GetString(2);
                            string gender = reader.GetString(3);
                            DateTime dob = reader.GetDateTime(4);
                            string address = reader.GetString(5);
                            string email = reader.GetString(6);
                            string phone = reader.GetString(7);
                            string description = reader.GetString(8);
                            Teacher t = new Teacher()
                            {
                                TeacherID = teacherID,
                                Password = password,
                                Name = name,
                                Gender = gender,
                                Dob = dob,
                                Address = address,
                                Email = email,
                                Phone = phone,
                                Description = description
                            };
                            list.Add(t);
                        }
                    }
                    return list;
                }
            }
            catch (SqlException e)
            {
                message = e.Message;
            }
            finally
            {
                if (reader != null)
                {
                    reader.Close();
                }
                if (connect != null)
                {
                    connect.Close();
                }
            }
            return null;
        }

        public static string Test()
        {
            try
            {
                using (connect = GetConnectionDB())
                {
                    connect.Open();
                    using (command = new SqlCommand("sp_GetAllManager", connect))
                    {
                        command.CommandType = CommandType.StoredProcedure;
                        reader = command.ExecuteReader();
                        reader.Read();
                        //string text = reader.GetString(0) + "---"+reader.GetString(1);

                        return reader.GetString(0);

                    }
                }
            }
            catch (SqlException)
            {
                message = "Can not find stored procedure !";
            }
            finally
            {
                if (reader != null)
                {
                    reader.Close();
                }
                if (connect != null)
                {
                    connect.Close();
                }
            }
            return null;
        }

        public static DataTable GetSelectTable(string storedProcedure, SqlParameter[] sqlParameter)
        {
            DataTable table = new DataTable();
            DataSet ds = new DataSet();
            SqlDataAdapter adapter = new SqlDataAdapter();
            try
            {
                using (connect = GetConnectionDB())
                {
                    connect.Open();
                    using (command = new SqlCommand(storedProcedure, connect))
                    {
                        command.CommandType = CommandType.StoredProcedure;
                        command.Parameters.AddRange(sqlParameter);
                        adapter.SelectCommand = command;
                        adapter.Fill(ds);
                        table = ds.Tables[0];
                    }

                }
            }
            catch (SqlException e)
            {
                message = e.Message;
                return null;
            }
            finally
            {
                if (reader != null)
                {
                    reader.Close();
                }
                if (connect != null)
                {
                    connect.Close();
                }
            }
            return table;
        }
        public static DataTable GetSelectTable(string storedProcedure)
        {
            DataTable table = new DataTable();
            DataSet ds = new DataSet();
            SqlDataAdapter adapter = new SqlDataAdapter();
            try
            {
                using (connect = GetConnectionDB())
                {
                    connect.Open();
                    using (command = new SqlCommand(storedProcedure, connect))
                    {
                        command.CommandType = CommandType.StoredProcedure;
                        adapter.SelectCommand = command;
                        adapter.Fill(ds);
                        table = ds.Tables[0];
                    }

                }
            }
            catch (SqlException e)
            {
                message = e.Message;
                return null;
            }
            finally
            {
                if (reader != null)
                {
                    reader.Close();
                }
                if (connect != null)
                {
                    connect.Close();
                }
            }
            return table;
        }
        public static bool InteractionData(string storedProcedure, SqlParameter[] sqlParameter)
        {
            int row = -1;
            try
            {
                using (connect = GetConnectionDB())
                {
                    connect.Open();
                    using (comand = new SqlCommand(storedProcedure, connect))
                    {
                        comand.CommandType = CommandType.StoredProcedure;
                        comand.Parameters.AddRange(sqlParameter);
                        row = comand.ExecuteNonQuery();
                    }
                }
            }
            catch (SqlException e)
            {
                message = e.Message;
                return false;
            }
            finally
            {
                if (connect != null)
                {
                    connect.Close();
                }
            }
            return row > 0;
        }
        // retry...non-use
        public static Avatar GetTeacherAvatar(string storedProcedure, SqlParameter[] sqlParameter)
        {
            Avatar a = null;
            SqlDataAdapter adapter = new SqlDataAdapter();
            try
            {
                using (connect = GetConnectionDB())
                {
                    connect.Open();
                    using (command = new SqlCommand(storedProcedure, connect))
                    {
                        command.CommandType = CommandType.StoredProcedure;
                        reader = comand.ExecuteReader();
                        reader.Read();
                        string teacherID = reader.GetString(0);

                        long bytesize = reader.GetBytes(0, 0, null, 0, 0);
                        byte[] imageData = new byte[bytesize];
                        long bytesread = 0;
                        int curpos = 0;
                        int chunkSize = 1;
                        while (bytesread < bytesize)
                        {
                            bytesread += reader.GetBytes(0, curpos, imageData, curpos, chunkSize);
                            curpos += chunkSize;
                        }
                        a = new Avatar()
                        {
                            UserID = teacherID,
                            UserAvatar = imageData
                        };
                    }

                }
            }
            catch (SqlException e)
            {
                message = e.Message;
                return null;
            }
            finally
            {
                if (reader != null)
                {
                    reader.Close();
                }
                if (connect != null)
                {
                    connect.Close();
                }
            }
            return a;
        }

        public static List<Student> GetAllStudent()
        {
            List<Student> lstStudents = new List<Student>();
            try
            {
                using (connect = GetConnectionDB())
                {
                    connect.Open();
                    using (command = new SqlCommand(Query.getAllStudent, connect))
                    {
                        command.CommandType = CommandType.StoredProcedure;
                        reader = command.ExecuteReader();
                        while (reader.Read())
                        {
                            string studentID = reader.GetString(0);
                            string name = reader.GetString(1);
                            string gender = reader.GetString(2);
                            DateTime dob = reader.GetDateTime(3);
                            string address = reader.GetString(4);
                            string email = reader.GetString(5);
                            string phone = reader.GetString(6);
                            string status = reader.GetString(7);

                            Student st = new Student()
                            {
                                StudentID = studentID,
                                Name = name,
                                Gender = gender,
                                Dob = dob,
                                Address = address,
                                Email = email,
                                Phone = phone,
                                Status = status
                            };
                            lstStudents.Add(st);
                        }
                    }
                    return lstStudents;
                }
            }
            catch (SqlException e)
            {
                message = e.Message;
            }
            finally
            {
                if (reader != null)
                {
                    reader.Close();
                }
                if (connect != null)
                {
                    connect.Close();
                }
            }
            return null;
        }

    }
} //end class


